package com.huabo.audit.oracle.mapper;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.huabo.audit.oracle.entity.TblAttachment;
import com.huabo.audit.oracle.entity.TblNbsjSheetEntity;
import com.huabo.audit.oracle.entity.TblNbsjSheetReportEntity;
import com.huabo.audit.oracle.vo.TBlNbsjSheetVo;
import com.huabo.audit.util.PageInfo;

public interface TblNbsjSheetMapper extends BaseMapper<TblNbsjSheetEntity> {
	@Select("SELECT * from TBL_NBSJ_SHEET WHERE SHEETID= #{sheetid} ")
    TblNbsjSheetEntity getById(String sheetid);
    
    @SelectProvider(method="selectCountByPageInfo",type=TblNbsjSheetMapperSqlConfig.class)
   	Integer selectCountByPageInfo(PageInfo<TblNbsjSheetEntity> pageInfo,TBlNbsjSheetVo tBlNbsjSheetVo) throws Exception;

    @SelectProvider(method="findByProjectIdAndPmUserId",type=TblNbsjSheetMapperSqlConfig.class)
    List<TblNbsjSheetEntity> findByProjectIdAndPmUserId(com.hbfk.util.PageInfo<TblNbsjSheetEntity> pageInfo,
			Integer projectId) throws Exception;

    @SelectProvider(method="selectCountByPageInfo",type=TblNbsjSheetMapperSqlConfig.class)
	Integer findByProjectIdAndPmUserIdCount(com.hbfk.util.PageInfo<TblNbsjSheetEntity> pageInfo, Integer projectId) throws Exception;
    
    @Select("SELECT TNA.*,PRINCIPAL.REALNAME,ORG.ORGNAME "
    		+ " FROM TBL_NBSJ_SHEET TNA "
    		+ "LEFT JOIN TBL_STAFF PRINCIPAL ON PRINCIPAL.STAFFID = TNA.CREATESTAFF "
			+ "LEFT JOIN TBL_ORGANIZATION ORG  ON ORG.ORGID = TNA.AUDITORG "
    		+ " WHERE TNA.SHEETID = #{sheetid}")
    @Results({
    	@Result(column="SHEETID",property="sheetId"),
    	@Result(column="SHEETCODE",property="sheetCode"),
    	@Result(column="SHEETNAME",property="sheetName"),
    	@Result(column="SHEETTARGET",property="sheetTarget"),
    	@Result(column="AUDITORG",property="auditOrg"),
    	@Result(column="RISKATTRBUTION",property="riskAttrbution"),
    	@Result(column="BUSINESSAFFILIATION",property="businessAffiliation"),
    	@Result(column="APPROVER",property="approver"),
    	@Result(column="RISKLEVEL",property="riskLevel"),
    	@Result(column="QUESTITLE",property="quesTitle"),
    	@Result(column="TARGETNAME",property="targetName"),
    	@Result(column="BUSINESSTYPE",property="businessType"),
    	@Result(column="SUDITPROCESS",property="suditProcess"),
    	@Result(column="ORGNAME",property="orgname"),
    	@Result(column="REALNAME",property="realname"),
    	@Result(column="CREATESTAFF",property="createStaff.staffid"),
    	
    	@Result(column="YJFH",property="yjfh"),
    	@Result(column="EJFH",property="ejfh"),
    	@Result(column="FIRSTSTAFFID",property="firststaffid"),
    	@Result(column="SECONDSTAFFID",property="secondstaffid"),
    	
    })
   	TblNbsjSheetEntity selectById(@Param("sheetid") Integer sheetid) throws Exception;
    
    @SelectProvider(method="selectListByPageInfo",type=TblNbsjSheetMapperSqlConfig.class)
    @Results({
    	@Result(column="SHEETID",property="sheetId"),
    	@Result(column="SHEETCODE",property="sheetCode"),
    	@Result(column="SHEETNAME",property="sheetName"),
    	@Result(column="SHEETTARGET",property="sheetTarget"),
    	@Result(column="AUDITORG",property="auditOrg"),
    	@Result(column="RISKATTRBUTION",property="riskAttrbution"),
    	@Result(column="BUSINESSAFFILIATION",property="businessAffiliation"),
    	@Result(column="APPROVER",property="approver"),
    	@Result(column="RISKLEVEL",property="riskLevel"),
    	@Result(column="QUESTITLE",property="quesTitle"),
    	@Result(column="TARGETNAME",property="targetName"),
    	@Result(column="BUSINESSTYPE",property="businessType"),
    	@Result(column="SUDITPROCESS",property="suditProcess"),
    	@Result(column="STATE",property="state"),
    	@Result(column="CREATESTAFF",property="createStaff.staffid"),
    	@Result(column="REALNAME",property="createStaff.realname"),
    	@Result(column="ORGNAME",property="orgname"),
    	@Result(column="REALNAME",property="realname"),
    	@Result(column="YJFH",property="yjfh"),
    	@Result(column="EJFH",property="ejfh"),
    	
    })
	List<TblNbsjSheetEntity> selectListByPageInfo(PageInfo<TblNbsjSheetEntity> pageInfo,TBlNbsjSheetVo tBlNbsjSheetVo) throws Exception;

    @Delete("DELETE FROM TBL_NBSJ_SHEET WHERE SHEETID = #{sheetid}")
    void deleteById(Integer sheetid) throws Exception;

    @SelectProvider(method="selectPlanCodeByOrgid",type=TblNbsjSheetMapperSqlConfig.class)
	Integer selectPlanCodeByOrgid(TblNbsjSheetEntity plan) throws Exception;
    
    @InsertProvider(method="insertEntity",type=TblNbsjSheetMapperSqlConfig.class)
    @Options(useGeneratedKeys=true, keyProperty="sheetId", keyColumn="SHEETID")
	void insertEntity(TblNbsjSheetEntity plan) throws Exception;

    @UpdateProvider(method="updateEntity",type=TblNbsjSheetMapperSqlConfig.class)
	void updateEntity(TblNbsjSheetEntity plan) throws Exception;

    @Update("update TBL_NBSJ_SHEET set firststaffid=#{firststaffid},set secondstaffid=#{secondstaffid} where sheetid=#{sheetid}")
	void saveSheetFh(Object object, Integer firststaffid, Integer secondstaffid,Integer sheetid);
    
    //
    @SelectProvider(method="getExportList",type=TblNbsjSheetMapperSqlConfig.class)
    @Results({
    	@Result(column="SHEETID",property="sheetId"),
    	@Result(column="SHEETCODE",property="sheetCode"),
    	@Result(column="SHEETNAME",property="sheetName"),
    	@Result(column="SHEETTARGET",property="sheetTarget"),
    	@Result(column="AUDITORG",property="auditOrg"),
    	@Result(column="RISKATTRBUTION",property="riskAttrbution"),
    	@Result(column="BUSINESSAFFILIATION",property="businessAffiliation"),
    	@Result(column="APPROVER",property="approver"),
    	@Result(column="RISKLEVEL",property="riskLevel"),
    	@Result(column="QUESTITLE",property="quesTitle"),
    	@Result(column="TARGETNAME",property="targetName"),
    	@Result(column="BUSINESSTYPE",property="businessType"),
    	@Result(column="SUDITPROCESS",property="suditProcess"),
    	@Result(column="REALNAME",property="realname"),
    	
    })
	List<TblNbsjSheetEntity> getExportList(Integer projectid) throws Exception;

    @Insert("INSERT INTO TBL_NBSJ_SHEETATT(SHEETID, ATTID) VALUES (#{workId}, #{attId})")
	void insetFileRelation(BigDecimal attId, Integer workId) throws Exception;
    
    
    //==
    @Delete("DELETE FROM TBL_NBSJ_SHEET_REPORT WHERE SHEETID = #{sheetId}")
    void deleteSheetReportBySheetId(Integer sheetId) throws Exception;
    
    @InsertProvider(method="insertSheetReport",type=TblNbsjSheetMapperSqlConfig.class)
    @Options(useGeneratedKeys=true, keyProperty="reportid", keyColumn="REPORTID")
	void insertSheetReport(TblNbsjSheetReportEntity sr) throws Exception;
    
    @Select("SELECT TNA.* "
    		+ " FROM TBL_NBSJ_SHEET_REPORT TNA "
    		+ " WHERE TNA.SHEETID = #{sheetid}")
    @Results({
    	@Result(column="REPORTID",property="reportid"),
    	@Result(column="REPORTCONTENT",property="reportConcent"),
    	@Result(column="REPORTORGIDS",property="sjdeptIds"),
    	@Result(column="SHEETID",property="sheetId"),
    })
   	List<TblNbsjSheetReportEntity> selectListSheetReport(@Param("sheetid") Integer sheetid) throws Exception;
    
    @Delete("DELETE FROM TBL_NBSJ_SHEET_REPORT WHERE REPORTID = #{reportid}")
    void deleteSheetReportById(Integer reportid) throws Exception;

    
    @Delete("DELETE FROM TBL_LEGAL_SHEET_ATT WHERE attid=#{attid}")
	void deleteFileInfoByAttId(Integer attid);

    @Select("select * from TBL_NBSJ_SHEET where SHEETID in (select SHEETID from TBL_NBSJ_QUESTIONAFFIRM f left join TBL_NBSJ_QUESTION q on f.QUESTIONID = q.QUESTIONID where q.QUESTIONID is not null and f.FACTID = #{factid}) and PROJECTID = #{projectId}")
	List<TblNbsjSheetEntity> findNbsjSheetByFactBookIdSp(String factid, String projectId);
    
}
